<?php
	
	// 连接数据库
	function connectSql(){
		//$con = mysqli_connect("localhost","root","root");
		$con = mysqli_connect("120.77.13.211","hx","1234567890");

		if (!$con) {
		  die('Could not connect: ' . mysql_error());
		} else {
			// echo '数据库连接成功';
		}

		// 选择哪个数据库
		$ret = mysqli_select_db($con, "mcsql");
		if ($ret) {
			// echo "<br>有这个数据库<br>";
		} else {
			echo "<br>没有这个数据库<br>";
		}

		return $con;
	}

	// 选择
	function selectTable($tb) {

	}

	// 插入用户信息
	function addMemberInfo($username, $nickname, $password, $avatar, $gender, $mobile) {

		$avatarUrl = "/mcnews/images/member/admin.png";

		if (!is_null($avatar)) {
			echo "avatar != null";
			$avatarUrl = "/mcnews/images/member/" . $avatar["name"]; 
			$path = $_SERVER["DOCUMENT_ROOT"] . $avatarUrl;
			move_uploaded_file($avatar["tmp_name"], $path);
		} else {
			echo "avatar == null";
		}
		
		$con = connectSql();
		$sql = "INSERT INTO mc_member (username, nickname, password, avatar, gender, mobile) VALUE ('" . $username . "', '" . $nickname . "', '" . $password . "', '" . $avatarUrl . "', " . $gender . ", '" . $mobile . "') ";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "用户写入成功<br>";
		} else {
			echo "用户写入失败<br>" . mysqli_error() . "<br>";
		}
	}

	// 修改用户信息
	function editMemberInfo($id, $username, $nickname, $password, $avatar, $gender, $mobile) {
		$con = connectSql();
		$sql = "UPDATE mc_member SET username = '" . $username . "', nickname = '" . $nickname . "', password = '" . $password . "', avatar = '" . $avatar . "', gender = " . $gender . ", mobile = '" . $mobile . "' WHERE id = " . $id . " ";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "用户信息修改成功";
		} else {
			echo "用户信息修改失败!" . mysqli_error();
		}

	}

	// 删除
	function delMemberInfo($id) {
		$con = connectSql();
		$sql = "DELETE FROM mc_member WHERE id=" . $id . "";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "该用户删除成功";
		} else {
			echo "该用户删除失败!" . mysqli_error();
		}
	}

	// 取出
	function getMemberInfo() {
		$con = connectSql();
		$sql = "SELECT * FROM mc_member Order by id";
		$ret = mysqli_query($con, $sql);

		$arr = array();
		while ($row = mysqli_fetch_array($ret)) {
			$arr[] = array("id"=>$row["id"],
						   "username"=>$row["username"],
						   "nickname"=>$row["nickname"],
						   "password"=>$row["password"],
						   "avatar"=>$row["avatar"],
						   "gender"=>$row["gender"],
						   "mobile"=>$row["mobile"]						   
							);
		}

		return $arr;
	}

	// 取出用户
	function getMemberInfoWithNum($num) {
		$con = connectSql();
		$sql = "SELECT * FROM mc_member LIMIT " . $num . "";
		$ret = mysqli_query($con, $sql);

		$arr = array();
		while ($row = mysqli_fetch_array($ret)) {
			$arr[] = array("id"=>$row["id"],
						   "username"=>$row["username"],
						   "nickname"=>$row["nickname"],
						   "password"=>$row["password"],
						   "avatar"=>$row["avatar"],
						   "gender"=>$row["gender"],
						   "mobile"=>$row["mobile"]						   
							);
		}

		return $arr;
	}

	// 根据手机号查找用户id
	function searchMemberFromMobile($mobile) {
		$con = connectSql();

		$sql = "SELECT * FROM mc_member WHERE mobile='" . $mobile . "'";
		$ret = mysqli_query($con, $sql);

		$arr = array();
		while ($row = mysqli_fetch_array($ret)) {
			$arr[] = array("id"=>$row["id"],
						   "username"=>$row["username"],
						   "nickname"=>$row["nickname"],
						   "password"=>$row["password"],
						   "avatar"=>$row["avatar"],
						   "gender"=>$row["gender"],
						   "mobile"=>$row["mobile"]						   
							);
		}
		return $arr;
	}


	// 分类
	// 插入分类
	function addClassInfo($parentId, $classname) {
		$con = connectSql();
		$sql = "INSERT INTO mc_news_type (parentId, classname) VALUE (" . $parentId . ", '" . $classname . "') ";
		$ret = mysqli_query($con, $sql);
		// 先判断该typeId是否已经存在，如果已经存在，return，提示typeId唯一
		if ($ret) {
			echo "分类写入成功<br>";
		} else {
			echo "分类写入失败<br>" . mysqli_error() . "<br>";
		}
	}

	// 修改分类信息
	function editClassInfo($id, $parentId, $classname) {
		$con = connectSql();
		$sql = "UPDATE mc_news_type SET parentId = " . $parentId . ", classname = '" . $classname . "' WHERE id = " . $id . " ";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "分类信息修改成功";
		} else {
			echo "分类信息修改失败!" . mysqli_error();
		}

	}

	// 删除
	function delClassInfo($id) {
		$con = connectSql();
		$sql = "DELETE FROM mc_news_type WHERE id=" . $id . "";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "该分类删除成功";
		} else {
			echo "该分类删除失败!" . mysqli_error();
		}
	}

	// 取出分类列表
	function getClassInfo() {
		$con = connectSql();
		$sql = "SELECT * FROM mc_news_type Order by parentId";
		$ret = mysqli_query($con, $sql);

		$arr = array();
		while ($row = mysqli_fetch_array($ret)) {
			$arr[] = array("id"=>$row["id"],
						   "parentId"=>$row["parentId"],
						   "classname"=>$row["classname"]				   
							);
		}

		return $arr;
	}

	// 文章
	// 插入文章
	function addContentInfo($classId, $title, $content, $memberId, $thumb) {

		if (!is_null($thumb)) {
			$thumbUrl =  "/mcnews/images/content/" . $thumb["name"]; 
			$path = $_SERVER["DOCUMENT_ROOT"] . $thumbUrl;
			move_uploaded_file($thumb["tmp_name"], $path);
		} else {

		}

		$con = connectSql();
		$sql = "INSERT INTO mc_news (classId, title, content, memberId, thumb) VALUE (" . $classId . ", '" . $title . "', '" . $content . "', " . $memberId . ", '" . $thumbUrl . "') ";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "文章写入成功<br>";
		} else {
			echo "文章写入失败<br>" . mysqli_error() . "<br>";
		}
	}

	// 修改文章信息
	function editContentInfo($id, $classId, $title, $content, $memberId, $thumb) {
		$con = connectSql();
		$sql = "UPDATE mc_news SET classId = " . $classId . ", title = '" . $title . "', content = '" . $content . "', memberId = " . $memberId . ", thumb = '" . $thumb . "' WHERE id = " . $id . " ";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "文章信息修改成功";
		} else {
			echo "文章信息修改失败!" . mysqli_error();
		}
	}

	// 取出文章列表
	function getContentInfo() {
		$con = connectSql();
		$sql = "SELECT * FROM mc_news Order by id";
		$ret = mysqli_query($con, $sql);

		$arr = array();
		while ($row = mysqli_fetch_array($ret)) {
			$arr[] = array("id"=>$row["id"],
						   "classId"=>$row["classId"],
						   "title"=>$row["title"],
						   "content"=>$row["content"],
						   "memberId"=>$row["memberId"],
						   "createTime"=>$row["createTime"],
						   "thumb"=>$row["thumb"]						   
							);
		}

		return $arr;
	}

	// 取出多少条文章列表
	function getContentInfoFrom($num) {
		$con = connectSql();
		$sql = "SELECT * FROM mc_news LIMIT " . $num . "";
		$ret = mysqli_query($con, $sql);

		$arr = array();
		while ($row = mysqli_fetch_array($ret)) {
			$arr[] = array("id"=>$row["id"],
						   "classId"=>$row["classId"],
						   "title"=>$row["title"],
						   "content"=>$row["content"],
						   "memberId"=>$row["memberId"],
						   "createTime"=>$row["createTime"],
						   "thumb"=>$row["thumb"]						   
							);
		}

		return $arr;
	}

	// 文章总数
	function getCountFromContent() {
		$con = connectSql();
		$sql = "SELECT COUNT(*) AS NumberOfNews FROM mc_news";
		$ret = mysqli_query($con, $sql);
		$count = 0;
		while ($row = mysqli_fetch_array($ret)) {
			$count = $row["NumberOfNews"];	
		}

		return $count;
	}

	function getListFromContent($offset, $row) {
		$con = connectSql();
		/*
		$sql = "SELECT mc_news.*, mc_news_type.classname, mc_member.nickname
				FROM mc_news 
				LEFT JOIN mc_news_type ON mc_news.classId = mc_news_type.id
				LEFT JOIN mc_member ON mc_news.memberId = mc_member.id 
				LIMIT " . $offset . "," . $row . "";
		*/
		$sql = "SELECT * FROM mc_news LIMIT " . $offset . "," . $row . "";
		$ret = mysqli_query($con, $sql);

		$arr = array();

		if (!$ret) {
			return $arr;
		}

		$classIds = "";
		$ids = "";
		while ($row = mysqli_fetch_array($ret)) {
			$classIds .= $row["classId"] . ",";
			$ids .= $row["memberId"] . ",";
			$arr[] = array("id"=>$row["id"],
						   "classId"=>$row["classId"],
						   "title"=>$row["title"],
						   "content"=>$row["content"],
						   "memberId"=>$row["memberId"],
						   "createTime"=>$row["createTime"],
						   "thumb"=>$row["thumb"],
						   "classname"=>"",
						   "nickname"=>""				   
							);
		}

		$classIds = substr($classIds,0,strlen($classIds)-1); 
		// ids (2, 4, 3)
		$sql = "SELECT classname,id FROM mc_news_type WHERE id in (" . $classIds . ")";
		$ret2 = mysqli_query($con, $sql);
		$arrClass = array();
		if ($ret2) {
			while ($row = mysqli_fetch_array($ret2)) {
				$arrClass[$row["id"]] = $row["classname"];
			}
		} 

		// ids (2, 4, 3)
		$ids = substr($ids,0,strlen($ids)-1); 
		$sql = "SELECT * FROM mc_member WHERE id in (" . $ids . ")";
		$ret3 = mysqli_query($con, $sql);		
		$arrMember = array();
		if ($ret3) {
			while ($row = mysqli_fetch_array($ret3)) {
				$arrMember[$row["id"]] = $row["nickname"];
			}
		}

		foreach ($arr as $key => $value) {
			$mId = $value["memberId"];
			$cId = $value["classId"];
			$arr[$key]["nickname"] = @$arrMember[$mId];
			$arr[$key]["classname"] = @$arrClass[$cId];
		 } 
		
		return $arr;
	}

	function getContentDetail($id) {
		$con = connectSql();
		$sql = "SELECT * FROM mc_news WHERE id=" . $id . "";
		$ret = mysqli_query($con, $sql);

		$arr = array();

		if (!$ret) {
			return $arr;
		}

		$ret = mysqli_fetch_array($ret);
		foreach ($ret as $key => $value) {
			$arr[$key] = $value;
		}

		return $arr;

	}

	// 删除
	function delContentInfo($id) {
		$con = connectSql();
		$sql = "DELETE FROM mc_news WHERE id=" . $id . "";
		$ret = mysqli_query($con, $sql);

		if ($ret) {
			echo "该文章删除成功";
		} else {
			echo "该文章删除失败!" . mysqli_error();
		}
	}

